package com.pcbly.web.persistence.table;

import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

import org.hibernate.Session;

import com.pcbly.web.bean.table.TableBean;
import com.pcbly.web.bean.table.TableChildBean;
import com.pcbly.web.components.utils.random.GUID;
import com.pcbly.web.persistence.HibernateBaseDao;

@SuppressWarnings("unchecked")
public class TableHibernateDao extends HibernateBaseDao<TableBean> implements
		TableDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3843906008171381367L;

	public TableHibernateDao() {

	}

	protected Class<TableBean> getEntityClass() {
		return TableBean.class;
	}

	@Override
	public List<TableBean> findTableBeansFromDatabase() throws Exception {
		// TODO Auto-generated method stub
		List<String> list = getSession()
				.createSQLQuery(
						"SELECT table_name FROM v_table where table_name not like '%_ext' ")
				.list();
		List<TableBean> beans = new ArrayList<TableBean>();
		for (String table : list) {
			TableBean bean = new TableBean();
			bean.setTableName(table);
			// System.out.println(table);
			// logger.debug(table);
			beans.add(bean);
		}
		return beans;
	}

	public String getTableBeanFromDataBase(String tableName) throws Exception {
		String tempTable = (String) getSession().createSQLQuery(
				"SELECT table_name FROM v_table where table_name=? ")
				.setString(0, tableName).uniqueResult();

		return tempTable;
	}

	@Override
	public void saveTableBean(TableBean bean) throws Exception {
		// TODO Auto-generated method stub
		bean.setId((new GUID()).toString());
		getHibernateTemplate().save(bean);
		StringBuilder sb = new StringBuilder();
		sb.append(" create table ");
		sb.append(bean.getTableName());
		sb.append(" (ID varchar(63) NOT NULL,");
		sb.append("CREATEUSERID varchar(63) ,");
		sb.append("RECYCLED_ varchar(1) ,");
		// sb.append(" add index(ID),");
		sb.append("PRIMARY KEY (ID))");
		getSession().createSQLQuery(sb.toString()).executeUpdate();
		sb = new StringBuilder();
		sb.append(" create table ");
		sb.append(bean.getTableNameExtention());
		sb.append(" (ID varchar(63) NOT NULL,");
		// sb.append(" add index(ID),");
		sb.append("PRIMARY KEY (ID))");
		getSession().createSQLQuery(sb.toString()).executeUpdate();
	}

	@Override
	public void saveTableBeans(List<TableBean> beans) throws Exception {
		// TODO Auto-generated method stub
		for (TableBean bean : beans) {
			bean.setId((new GUID()).toString());
			if (bean.getTableName().trim().toLowerCase().indexOf("t_") >= 0) {
				bean.setTableNameExtention(bean.getTableName().trim().concat(
						"_ext"));
			} else {
				bean.setTableName("t_".concat(bean.getTableName()));
				bean.setTableNameExtention("t_".concat(bean.getTableName())
						.concat("_ext"));
			}
			getHibernateTemplate().save(bean);
		}
	}

	@Override
	public void updateTableBean(TableBean bean) throws Exception {
		// TODO Auto-generated method stub
		try {
			getHibernateTemplate().update(bean);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	@Override
	public void updateTableBeans(List<TableBean> beans) throws Exception {
		// TODO Auto-generated method stub
		for (TableBean bean : beans) {
			if (bean.getTableName().trim().toLowerCase().indexOf("t_") >= 0) {
				bean.setTableNameExtention(bean.getTableName().trim().concat(
						"_ext"));
			} else {
				bean.setTableNameExtention("t_".concat(bean.getTableName())
						.concat("_ext"));
			}
			getHibernateTemplate().update(bean);
		}
	}

	@Override
	public void deleteTableBeans(List<TableBean> beans) throws Exception {
		// TODO Auto-generated method stub
		for (TableBean bean : beans) {
			if (!(bean.getTableName() == null || "".equals(bean.getTableName()
					.trim()))) {
				getSession().createSQLQuery(
						" drop table " + bean.getTableName()).executeUpdate();
			}
			if (!(bean.getTableNameExtention() == null || "".equals(bean
					.getTableNameExtention().trim()))) {
				getSession().createSQLQuery(
						" drop table " + bean.getTableNameExtention())
						.executeUpdate();
			}
			getHibernateTemplate().delete(bean);
		}
	}

	@Override
	public void deleteTableBeansById(String id) throws Exception {
		// TODO Auto-generated method stub
		String sql = "delete from t_table  where ID in('"
				+ id.replaceAll(",", "','") + "')";
		Session session = getSession();

		List<TableBean> beans = getSession().createSQLQuery(
				"select * from t_table where ID in('"
						+ id.replaceAll(",", "','") + "')").addEntity(
				TableBean.class).list();
		for (TableBean bean : beans) {
			if (!(bean.getTableName() == null || "".equals(bean.getTableName()
					.trim()))) {
				session.createSQLQuery(" drop table " + bean.getTableName())
						.executeUpdate();
			}
			if (!(bean.getTableNameExtention() == null || "".equals(bean
					.getTableNameExtention().trim()))) {
				session.createSQLQuery(
						" drop table " + bean.getTableNameExtention())
						.executeUpdate();
			}
		}
		session.createQuery(sql).executeUpdate();
		sql = "delete from  t_table_column  where TABLEID in('"
				+ id.replaceAll(",", "','") + "')";
		session.createQuery(sql).executeUpdate();
	}

	@Override
	public List<TableChildBean> findTableChildBeans() throws Exception {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("select a.id,a.tablename,a.tablecname ");
		sb.append(",'2' CHILD_ from ");
		sb.append(" t_table a   ");
		ResourceBundle rb = ResourceBundle.getBundle("lightsoft");
		// if (!(rb.getString("table.lockTable").indexOf("0") >= 0)) {
		String sbSql = rb.getString("table.lockTable");
		if (!(sbSql == null || "".equals(sbSql.trim()))) {
			sb.append(" where a.tablename not in('");
			sbSql = sbSql.replaceAll(",", "','");
			sb.append(sbSql);
			sb.append("')");
		}
		// }
		sb.append(" order by a.tablename ");
		List<TableChildBean> list = getSession().createSQLQuery(sb.toString())
				.addEntity(TableChildBean.class).list();
		return list;
	}

	@Override
	public List<TableBean> getTableBeans() throws Exception {
		// TODO Auto-generated method stub
		return getSession().createQuery("from TableBean").list();
	}

	@Override
	public boolean checkName(String name) throws Exception {
		// TODO Auto-generated method stub
		TableBean bean = (TableBean) getSession().createQuery(
				"from TableBean where upper(tableName)=? ").setString(0,
				name.toUpperCase()).uniqueResult();
		if (bean.getTableName() == null) {
			return false;
		}
		return bean.getTableName().equals(name);
	}

	@Override
	public List<TableBean> findTableBeansByTableIds(String tableIds)
			throws Exception {
		// TODO Auto-generated method stub
		List<TableBean> beans = new ArrayList<TableBean>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from t_table  where ID in(");
		sb.append("'");
		sb.append(tableIds.replaceAll(",", "','"));
		sb.append("')");
		// logger.debug(sb);
		// System.out.println(sb);
		beans = getSession().createSQLQuery(sb.toString()).addEntity(
				TableBean.class).list();
		return beans;
	}

}
